﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace AlarmClock
{
    public class DataBase
    {
        private String dbName;
        private String Password;

        private SQLiteConnection sqliteConnection;
        private SQLiteConnectionStringBuilder connStrBu;
        private SQLiteCommand sqliteCmd;
        private String querySql;

        private int errorFlag=-1;
        private bool isDBOpen=false;

        private String[] Error = { "创建数据库失败",
                                     "创建表失败", 
                                     "打开数据库失败",
                                     "打开表失败", 
                                     "添加数据失败", 
                                     "修改数据失败", 
                                     "查询数据失败", 
                                     "未找到所有查询的数据", 
                                     "索引超出范围",
                                     "修改数据失败", 
                                     "修改数据失败，所有修改的项不存在", 
                                     "删除数据失败", 
                                     "删除数据失败，所有删除的数据项不存在" };

        public DataBase(String dbname, String password)
        {
            this.dbName = dbname;
            this.Password = password;
            if (!File.Exists(dbname))
            {
                SQLiteConnection.CreateFile(dbName);
            }
            sqliteConnection = new SQLiteConnection();
            connStrBu = new SQLiteConnectionStringBuilder();
            sqliteCmd = new SQLiteCommand();

        }
        
        public String getErrorStr()
        {
            return Error[errorFlag];
        }

        public void createTable(String tableName,String[]volumeName,ValueType[]valuesType)
        {
            int volumeLength = volumeName.Length;
            if (volumeLength < 1 || volumeLength != valuesType.Length)
            {
                errorFlag = 1;
            }
            else
            {
                querySql = "create table if not exists " + tableName + "(";
                int i = 0;
                for (; i < volumeLength-1; i++)
                {
                    querySql += volumeName[i] + " " + valuesType[i]+",";
                }
                querySql += volumeName[i] + " " + valuesType[i] + ")";
                try
                {
                    openDB();
                    sqliteCmd.CommandText = querySql;
                    sqliteCmd.Connection = sqliteConnection;
                    sqliteCmd.ExecuteNonQuery();
                    errorFlag = -1;
                    closeDB();
                }
                catch
                {
                    errorFlag = 1;
                }
            }
        }
        
        public void createTable(int flag)
        {
            if (flag == 1)
            {
                //flag=1:创建铃声表
                querySql = "create table if not exists Music(Id integer,Name varchar,Path varchar)";
            }
            else if (flag == 2)
            {
                //flag=2:创建闹钟时间表
                querySql = "create table if not exists AlarmTime(Id integer,Date varchar,Time varchar,MusicId integer)";
            }
            else if (flag == 3)
            {
                //flag=3:创建倒计时表
                querySql = "create table if not exists CountDown(Id integer,Time varchar,Lable varchar)";
            }
            else
                return;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 1;
            }
        }
        
        public int getCount(int flag)
        {
            int count = 0;
            if (flag == 1)
            {
                querySql = "select count(*) count from Music";
            }
            else if (flag == 2)
            {
                querySql = "select count(*) count from AlarmTime";
            }
            else if (flag == 3)
            {
                querySql = "select count(*) count from CountDown";
            }
            else
                return 0;
            SQLiteDataReader reader=null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read()) 
                    count = reader.GetInt16(0);
                else count = 0;
                reader.Close();
                closeDB();
                return count;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
                return 0;
            }
        }
        //添加铃声
        public void addMusic(int count,String name, String path)
        {
            querySql = "insert into Music values(@count,@name,@path)";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Parameters.AddWithValue("@count", count);
                sqliteCmd.Parameters.AddWithValue("@name", name);
                sqliteCmd.Parameters.AddWithValue("@path", path);
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 4;
            }
        }
        //添加铃声
        public void addMusic(int count,Music music)
        {
            addMusic(count,music.name, music.path);
        }
        //删除铃声 
        public void deleteMusic(int id)
        {
            if (id < 0 || id > getCount(1))
            {
                errorFlag = 8;
                return;
            }
            querySql = "delete from Music where Id="+id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //删除所有铃声 
        public void deleteAllMusic()
        {
            querySql = "delete from Music";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //修改铃声
        public void alterMusic(int id,String name, String path)
        {
            if (id < 0 || id > getCount(1))
            {
                errorFlag = 10;
                return;
            }
            querySql = "update Music set Name='" + name + "',Path='" + path + "' where Id=" + id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //查询铃声
        public String findMusic(int id)
        {
            DataTable dt = new DataTable();
            querySql = "select path from Music where Id=" + id;
            String path="";
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if(reader.Read())
                {
                    path = reader.GetString(0);
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return path;
        }
        //查询所有铃声
        public DataTable findAllMusicToDT()
        {
            DataTable dt = new DataTable();
            querySql = "select * from Music";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                SQLiteDataAdapter sqliteDataAdapter = new SQLiteDataAdapter(sqliteCmd);
                sqliteDataAdapter.Fill(dt);
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 6;
            }
            return dt;
        }
        //查询所有铃声
        public List<Music> getMusicList()
        {
            List<Music> musicList = new List<Music>();
            querySql = "select * from Music";
            SQLiteDataReader reader = null;
            try
            {
                Music music;
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                while (reader.Read())
                {
                    music = new Music();
                    music.name=reader.GetString(1);
                    music.path=reader.GetString(2);
                    musicList.Add(music);
                }
                reader.Close();
                closeDB();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return musicList;
        }
        //查询所有铃声
        public String[] getMusicNameList()
        {
            String[] musicNameList=new String[getCount(1)];
            querySql = "select Name from Music";
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    musicNameList[i] = reader.GetString(0);
                    i++;
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return musicNameList;
        }
        //查看某歌曲是否已添加
        public bool isMusicExist(string name)
        {
            name = name.Replace("'","*");
            int count = -1;
            querySql = "select count(*) from Music where Name like '"+name+"'";
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read()) count = reader.GetInt16(0);
                else count = 0;
                reader.Close();
                closeDB();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
                return false;
            }
            if (count > 0)
                return true;
            else return false;
        }
        //添加闹钟
        public void addAlarm(String date, String time, int musicid)
        {
            querySql = "insert into AlarmTime values(" + getCount(2) + ",'" + date + "','" + time + "'," + musicid + ")";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 4;
            }
        }
        //删除闹钟
        public void deleteAlarm(int id)
        {
            if (id < 0 || id > getCount(2))
            {
                errorFlag = 8;
                return;
            }
            querySql = "delete from AlarmTime where Id=" + id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //删除所有闹钟
        public void deleteAllAlarm()
        {
            querySql = "delete from AlarmTime";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //修改闹钟
        public void alterAlarm(int id, String date, String time,int musicid)
        {
            if (id < 0 || id > getCount(2))
            {
                errorFlag = 10;
                return;
            }
            querySql = "update AlarmTime set Date='" + date + "',Time='" + time + "',MusicId=" + musicid + " where Id=" + id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //删除铃声时修改闹钟
        public void alterAlarm(int musicid)
        {
            querySql = "update AlarmTime set MusicId=-1 where MusicId=" + musicid + ";update Music set Id=Id-1 where Id>" + musicid + ";update AlarmTime set MusicId=MusicId-1 where MusicId>" + musicid;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //查询所有闹钟
        public DataTable findAllAlarm()
        {
            DataTable dt = new DataTable();
            querySql = "select * from AlarmTime";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                SQLiteDataAdapter sqliteDataAdapter = new SQLiteDataAdapter(sqliteCmd);
                sqliteDataAdapter.Fill(dt);
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 6;
            }
            return dt;
        }
        //查询所有闹钟
        public List<Alarm> getAlarmList()
        {
            List<Alarm> alarmList = new List<Alarm>();
            querySql = "select * from AlarmTime";
            SQLiteDataReader reader = null;
            try
            {
                Alarm alarm;
                String dateStr = "";
                String[] dateStrs;
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                while (reader.Read())
                {
                    alarm = new Alarm();
                    dateStr = reader.GetString(1);
                    alarm.time = reader.GetString(2);
                    alarm.musicId = reader.GetInt16(3);
                    dateStrs = dateStr.Split('/');
                    alarm.date = new DateTime(Convert.ToInt16(dateStrs[0]), Convert.ToInt16(dateStrs[1]), Convert.ToInt16(dateStrs[2]), 0, 0, 0);
                    alarmList.Add(alarm);
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return alarmList;
        }
        //添加倒计时
        public void addCountDown(String time, String lable)
        {
            querySql = "insert into CountDown values(" + getCount(3) + ",'" + time + "','" + lable + "')";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 4;
            }
        }
        //添加倒计时
        public void addCountDown(int id,String time, String lable)
        {
            querySql = "insert into CountDown values(" + id + ",'" + time + "','" + lable + "')";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 4;
            }
        }
        //删除倒计时
        public void deleteCountDown(int id)
        {
            if (id < 0 || id > getCount(3))
            {
                errorFlag = 8;
                return;
            }
            querySql = "delete from CountDown where Id=" + id+";update CountDown set Id=Id-1 where Id>"+id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //插入倒计时
        public void insertCountDown(int id, String time, String lable)
        {
            querySql = "update CountDown set Id=Id+1 where Id>="+id+";insert into CountDown values(" + id + ",'" + time + "','" + lable + "')";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 4;
            }
        }
        //删除所有倒计时
        public void deleteAllCountDown()
        {
            querySql = "delete from CountDown";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 11;
            }
        }
        //修改倒计时
        public void alterCountDown(int id, String time, String lable)
        {
            if (id < 0 || id > getCount(3))
            {
                errorFlag = 10;
                return;
            }
            querySql = "update CountDown set Time='" + time + "',Lable='" + lable + "' where Id=" + id;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //修改倒计时Id
        public void alterCountDownId(int id,String lable,int newId)
        {
            if (id < 0 || id > getCount(3))
            {
                errorFlag = 10;
                return;
            }
            querySql = "update CountDown set id=" + newId + " where Id=" + id+" and lable like '"+lable+"'";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //倒计时Id加1
        public void addCountDownId(int startId)
        {
            if (startId < 0 || startId > getCount(3))
            {
                errorFlag = 10;
                return;
            }
            querySql = "update CountDown set id=id+1 where Id>=" + startId;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                sqliteCmd.ExecuteNonQuery();
                errorFlag = -1;
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 9;
            }
        }
        //查询倒计时
        public CountDown getCountDown(int id)
        {
            CountDown countDown = new CountDown();
            querySql = "select * from CountDown where Id="+id;
            SQLiteDataReader reader = null;
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                if (reader.Read())
                {
                    String dateStr = "";
                    countDown.id = reader.GetInt16(0);
                    dateStr = reader.GetString(1);
                    countDown.lable = reader.GetString(2);
                    countDown.time = new DateTime(long.Parse(dateStr));
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return countDown;
        }
        //查询所有倒计时
        public DataTable findAllCountDown()
        {
            DataTable dt = new DataTable();
            querySql = "select * from CountDown";
            try
            {
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                SQLiteDataAdapter sqliteDataAdapter = new SQLiteDataAdapter(sqliteCmd);
                sqliteDataAdapter.Fill(dt);
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                errorFlag = 6;
            }
            return dt;
        }
        //查询所有倒计时
        public List<CountDown> getCountDownList()
        {
            List<CountDown> countDownList = new List<CountDown>();
            querySql = "select * from CountDown order by Time";
            SQLiteDataReader reader = null;
            try
            {
                CountDown countDown;
                String dateStr = "";
                openDB();
                sqliteCmd.CommandText = querySql;
                sqliteCmd.Connection = sqliteConnection;
                reader = sqliteCmd.ExecuteReader();
                while (reader.Read())
                {
                    countDown = new CountDown();
                    countDown.id = reader.GetInt16(0);
                    dateStr = reader.GetString(1);
                    countDown.lable= reader.GetString(2);
                    countDown.time = new DateTime(long.Parse(dateStr));
                    countDownList.Add(countDown);
                }
                reader.Close();
                closeDB();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                reader.Close();
                errorFlag = 6;
            }
            return countDownList;
        }
        //打开数据库
        public void openDB()
        {
            if (!isDBOpen)
            {
                connStrBu.DataSource = dbName;
                connStrBu.Password = Password;//设置密码，SQLite ADO.NET实现了数据库密码保护

                sqliteConnection.ConnectionString = connStrBu.ToString();

                sqliteConnection.Open();
                isDBOpen = true;
            }
        }
        //关闭数据库
        public void closeDB()
        {
            if (isDBOpen)
            {
                sqliteConnection.Close();
                isDBOpen = false;
            }
        }
    }
}